﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using SFrost;
using System.Text;
using System.Configuration;
using System.Web.UI;  //引用后，就可以用了
using System.Data;
using ReportMobile.admin.inc;

//测试属性是否可以用，请在【方法】中使用，例如：Request
//非静态方法可以调用静态属性，但是静态方法，不能调用非静态属性和方法

namespace ReportMobile.inc {
    /// <summary>
    /// 客户借款类型（未用）
    /// </summary>
    public enum BalanceType {
        客户临时借款,
        客户贷款
    }
    public enum UnitName {
        事业部 = 1,
        川渝区 = 2,
        非川渝 = 3,
        分公司 = 4
    }
    //第二个枚举始终提示不存在
    //解决办法：重新编译程序
    public enum UnitUrl {
        InBU = 1,
        InCYQ = 2,
        InNotCYQ = 3,
        InCustom = 4
    }
    public class Common : Page{
        public string he,msg = "";
        public DataSet ds = new DataSet();
        public DataTable dt = new DataTable();
        public DataRow dr;
        public SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnName"].ToString());
        public SqlCommand cmd = new SqlCommand();
        public Common() {
            cmd.Connection = conn;
            conn.Open();
        }

        /// <summary>
        /// 执行存储过程，返回DataTable
        /// </summary>
        /// <param name="proc">存储过程名称</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteProcedure(string proc) {
            if (proc.Length < 1)
                return null;
            cmd.Dispose();
            //StringBuilder sb = new StringBuilder();
            //sb.AppendFormat("execute {0}", proc);
            cmd.CommandText = proc;
            try {
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                dt.Clear(); //必须清空DT，否则一个页面多次调用会累加数据
                sda.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception er) {
                msg = er.Message + cmd.CommandText.ToString();
                conn.Close();
                return null;
            }
        }
        /// <summary>
        /// 20160922-川渝区的欠款余额明细
        /// </summary>
        /// <param name="variety">品类名称</param>
        /// <param name="type">欠款类型</param>
        /// <returns></returns>
        public DataTable GetBalanceDetail_CYQ(string variety, string type) {
            StringBuilder sb = new StringBuilder();
            sb.Append("select 公司简称,客户名称,借贷时间,归还时间,欠款余额 from View_ES_客户借贷款明细");
            sb.Append(" where 1=1");
            sb.Append(" AND 品类 NOT IN ('放养鸭', '鸭料（非放养）')");
            sb.Append(" and 制表单位 IN ( '成都铁骑力士饲料有限公司A','绵阳铁骑力士饲料有限公司','广元铁骑力士饲料有限公司','四川铁骑力士实业有限公司乐山分公司','重庆铁骑力士牧业科技有限公司','四川铁骑力士实业有限公司涪陵分公司','四川铁骑力士实业有限公司绵阳饲料部')");
            if (variety != null) {
                sb.Append(" and 品类 = '" + variety + "'");
                sb.Append(" and 借款类型 = '" + type.ToString() + "'");
            }
            sb.Append(" order by 公司简称,客户名称");
            cmd.Dispose();
            cmd.CommandText = sb.ToString();
            ds.Clear();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 20160922-非川渝区的欠款余额明细
        /// </summary>
        /// <param name="area">分公司名称</param>
        /// <param name="type">欠款类型</param>
        /// <returns></returns>
        public DataTable GetBalanceDetail_FCY(string area, string type) {
            StringBuilder sb = new StringBuilder();
            sb.Append("select 公司简称,客户名称,借贷时间,归还时间,欠款余额 from View_ES_客户借贷款明细");
            if(area != null) {
                sb.Append(" where 公司简称 = '" + area + "'");
                sb.Append(" and 借款类型 = '" + type.ToString() + "'");
            }
            sb.Append(" order by 公司简称,客户名称");
            cmd.Dispose();
            cmd.CommandText = sb.ToString();
            ds.Clear();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 显示底部导航条，用于返回上一页、主页
        /// </summary>
        /// <returns></returns>
        public string DisplayMenuOfBottom() {
            StringBuilder sbMenu = new StringBuilder();
            sbMenu.Append("<div class=\"navbar navbar-fixed-bottom sf_menu\">");
            sbMenu.Append("<ul class=\"nav nav-pills\">");
            sbMenu.Append("<li role=\"presentation\"><a href=\"javascript:history.go(-1);\">后退</a></li>");
            sbMenu.Append("<li role=\"presentation\"><a href=\"SYB_index.aspx\">首页</a></li>");
            sbMenu.Append("</ul></div>");
            return sbMenu.ToString();
        }

        /// <summary>
        /// 记录用操作日志
        /// </summary>
        /// <param name="strOaId">OA账号</param>
        /// <param name="strModule">操作模块</param>
        /// <param name="dtimeView">操作时间</param>
        public void SF_UserLog(string strOaId,string strModule,DateTime dtimeView,string strUserAgent = null) {
            if (conn.State.ToString() == "Closed") conn.Open();
            string strUserName = "";
            string strIP = Swift.GetClientIP();
            //string strUserAgent = Request.UserAgent;
            if (strOaId.Trim().Length > 1) {
                StringBuilder sb = new StringBuilder("select fname from tauth");
                sb.AppendFormat(" where foaid = '{0}'", strOaId);
                cmd.Dispose();
                ds.Clear();
                cmd.CommandText = sb.ToString();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
                dt = ds.Tables[0];
                dr = dt.Rows[0];
                strUserName = dr["fname"].ToString();
                sb.Clear();
                sb.Append("insert into tlog (fname,foaid,fmodule,fviewtime,fipaddress,fuseragent)");
                sb.AppendFormat(" values ('{0}','{1}','{2}','{3}','{4}','{5}')", strUserName, strOaId, strModule, dtimeView.ToString(),strIP,strUserAgent);
                cmd.CommandText = sb.ToString();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            //conn.Close();
        }
        /// <summary>
        /// null：取事业部，非null：取指定公司
        /// </summary>
        /// <param name="sbCompany">公司，以半角逗号分隔</param>
        /// <returns>dOUBLE</returns>
        public double GetSumByCompany(StringBuilder sbCompany = null) {
            double re = -1;
            try {
                if (sbCompany == null) {
                    string sqlStr = "select SUM(欠款余额) 欠款余额 from View_OA_客户借贷款 where 欠款余额 >= 0";
                    cmd.Dispose();
                    cmd.CommandText = sqlStr;
                    ds.Clear();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    dt = ds.Tables[0];
                    dr = dt.Rows[0];
                    conn.Close();
                    return Convert.ToDouble(dr["欠款余额"]);
                }
                else {
                    StringBuilder sb = new StringBuilder("select SUM(欠款余额) 欠款余额 from View_OA_客户借贷款");
                    sb.AppendFormat(" where 制表单位 in ({0})", sbCompany.ToString());
                    sb.Append(" and 欠款余额 >= 0");
                    cmd.Dispose();
                    cmd.CommandText = sb.ToString();
                    ds.Clear();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    dt = ds.Tables[0];
                    dr = dt.Rows[0];
                    conn.Close();
                    return Convert.ToDouble(dr["欠款余额"]);
                }
            }
            catch {
                return re;
            }
        }
        /// <summary>
        /// 传入SQL语句，执行并返回受影响行数（Insert、Update）
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public int SqlWithSB(StringBuilder sb)
        {
            cmd.Dispose();
            cmd.CommandText = sb.ToString();
            //if (cmd.ExecuteNonQuery() == null) return -1;
            int i = cmd.ExecuteNonQuery();
            if ( i < 1)
            {
                return -2;
            }
            else
            {
                return i;
            }
        }
        /// <summary>
        /// 传入SB类型（ID、OAID），判断指定的记录是否存在
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public bool IsHaveUser(StringBuilder sb)
        {
            cmd.Dispose();
            ds.Clear();
            cmd.CommandText = sb.ToString();
            if (cmd.ExecuteScalar() == null) return false;
            if ((int)(cmd.ExecuteScalar()) < 1)
            {
                conn.Close();
                return false;
            }
            conn.Close();
            return true;
        }
        /// <summary>
        /// 传入ID（非OAID），判断用户是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsHaveUser(string uid)
        {
            cmd.Dispose();
            ds.Clear();
            StringBuilder sbStr = new StringBuilder("select FId, FOAId, FCompany, FVariety, FRegDate,funitid,funitcustom from TAuth where fid = '" + uid + "'");
            cmd.CommandText = sbStr.ToString();
            if (cmd.ExecuteScalar() == null) return false;
            if ((int)(cmd.ExecuteScalar()) < 1)
            {
                conn.Close();
                return false;
            }
            conn.Close();
            return true;
        }
        /// <summary>
        /// 调用前，请使用IsHaveUser判断用户是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetUserAuth(string id)
        {
            cmd.Dispose();
            ds.Clear();
            StringBuilder sbStr = new StringBuilder("select fname,FId, FOAId, FCompany, FVariety, FRegDate,funitid,funitcustom from TAuth where fid = '" + id + "'");
            cmd.CommandText = sbStr.ToString();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
                dt = ds.Tables[0];
                return dt.Rows[0];
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 顶部导航菜单
        /// </summary>
        /// <param name="loginUserId">登录用户OA账号</param>
        /// <param name="activeUnitId">激活【当前】菜单状态</param>
        /// <returns></returns>
        public string GetMenu(string loginUserId, string activeUnitId) {
            //string classActiveUnit = "";
            if (loginUserId == null || loginUserId.Trim().Length < 1) {
                msg = "请使用正确的用户名登录，联系OA管理员。";
                return msg;
            }

            StringBuilder sbStr = new StringBuilder("select FId, FOAId, FCompany, FVariety, FRegDate,funitid,funitcustom from TAuth where foaid = '" + loginUserId + "'");
            cmd.Dispose();
            cmd.CommandText = sbStr.ToString();
            if ((int)(cmd.ExecuteScalar()) < 1) {
                msg = "未查找到用户：" + loginUserId + " 的授权，请与系统管理员联系。";
                return msg;
            }
            try {
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                ds.Clear();
                sda.Fill(ds);
                dt = ds.Tables[0];
                dr = dt.Rows[0];
                string[] authUnitId = dr["funitid"].ToString().Split(',');
                StringBuilder menuHtmlCode = new StringBuilder("<ul class=\"nav nav-pills\">\n");
                if (activeUnitId == null || activeUnitId.Trim().Length < 1) {
                    menuHtmlCode.Append("\t<li role=\"presentation\" class=\"active jiachu\"><a href=\"Index.aspx?loginid=" + loginUserId + "\">首页</a>");
                }
                else {
                    menuHtmlCode.Append("\t<li role=\"presentation\"><a href=\"Index.aspx?loginid=" + loginUserId + "\">首页</a>");
                }
                for (int i = 0; i < authUnitId.Length; i++) {
                    //修改链接地址，传入点击页面的ID号，判断权限
                    if (activeUnitId == authUnitId[i]) {
                        menuHtmlCode.Append("\t<li role=\"presentation\" class=\"active\"><a href=\"" + (UnitUrl)(Convert.ToInt32(authUnitId[i])) + ".aspx?uid=" + loginUserId + "&unitid=" + authUnitId[i] + "\">" + (UnitName)(Convert.ToInt32(authUnitId[i])) + "</a></li>\n");
                    }
                    else {
                        menuHtmlCode.Append("\t<li role=\"presentation\"><a href=\"" + (UnitUrl)(Convert.ToInt32(authUnitId[i])) + ".aspx?uid=" + loginUserId + "&unitid=" + authUnitId[i] + "\">" + (UnitName)(Convert.ToInt32(authUnitId[i])) + "</a></li>\n");
                    }
                }
                menuHtmlCode.Append("</ul>\n");
                cmd.Dispose();
                conn.Close();
                return menuHtmlCode.ToString();
            }
            catch (Exception sfe) {
                return sfe.Message;
            }            
        }
        /// <summary>
        /// 非川渝区：按分公司维度，取超期未还明细
        /// </summary>
        /// <param name="dateExpire">截止时间</param>
        /// <param name="strExpireCompany">制表单位，非简称</param>
        /// <param name="strExpireType">借款类型</param>
        /// <returns></returns>
        public DataTable GetExpireDetail(DateTime dateExpire, string strExpireCompany, string strExpireType = "") {
            StringBuilder sbQuotaDetail = new StringBuilder();
            sbQuotaDetail.Append("select * from View_ES_客户借贷款明细");
            sbQuotaDetail.Append(" where 1 = 1");
            sbQuotaDetail.AppendFormat(" and 归还时间 < '{0}'", dateExpire);
            sbQuotaDetail.AppendFormat(" and 制表单位 = '{0}'", strExpireCompany);
            if (strExpireType != "") {
                sbQuotaDetail.AppendFormat(" and 借款类型 = '{0}'", strExpireType);
            }
            sbQuotaDetail.Append(" order by 归还时间 desc");
            try {
                cmd.Dispose();
                ds.Clear();
                cmd.CommandText = sbQuotaDetail.ToString();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);   //不能添加给ds，然后再返回ds.table[0]，前端使用会报错
                conn.Close();
                return dt;
            }
            catch (Exception sfe) {
                msg = sfe.Message + sbQuotaDetail.ToString();
                conn.Close();
                return null;
            }
        }

        /// <summary>
        /// 川渝区：按品类，借款类型，获取指定公司范围的超期未还明细，返回DataTable
        /// </summary>
        /// <param name="dateExpire">截止时间</param>
        /// <param name="sbExpireVariety">品类</param>
        /// <param name="sbExpireCompany">川渝公司范围</param>
        /// <param name="sbExpireType">借款类型，默认为空</param>
        /// <returns></returns>
        public DataTable GetExpireDetail(DateTime dateExpire, string sbExpireVariety, StringBuilder sbExpireCompany, string sbExpireType = "") {
            StringBuilder sbQuotaDetail = new StringBuilder();
            sbQuotaDetail.Append("select * from View_ES_客户借贷款明细");
            sbQuotaDetail.AppendFormat(" where 制表单位 in ({0})", sbExpireCompany.ToString());
            sbQuotaDetail.AppendFormat(" and 归还时间 < '{0}'", dateExpire);
            sbQuotaDetail.AppendFormat(" and 品类 = '{0}'", sbExpireVariety);
            if (sbExpireType != "") {
                sbQuotaDetail.AppendFormat(" and 借款类型 = '{0}'", sbExpireType);
            }
            sbQuotaDetail.Append(" order by 公司简称,归还时间 desc");
            try {
                cmd.Dispose();
                ds.Clear();
                cmd.CommandText = sbQuotaDetail.ToString();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);   //不能添加给ds，然后再返回ds.table[0]，前端使用会报错
                conn.Close();
                return dt;
            }
            catch (Exception sfe){
                msg = sfe.Message + sbQuotaDetail.ToString();
                conn.Close();
                return null;
            }
        }

        /// <summary>
        /// 非川渝区：按公司，取指定类型的超期未还金额
        /// </summary>
        /// <param name="dateExpire">统计时间</param>
        /// <param name="strExpireCompany">制表单位</param>
        /// <param name="strExpireType">借款类型</param>
        /// <returns></returns>
        public double GetExpireLimit(DateTime dateExpire, string strExpireCompany, string strExpireType) {
            StringBuilder sbRpt = new StringBuilder("select 制表单位,借款类型,SUM(欠款余额) 超期未还 from View_ES_客户借贷款明细");
            sbRpt.AppendFormat(" where 制表单位 = '{0}'", strExpireCompany);
            sbRpt.AppendFormat(" and 借款类型 = '{0}'", strExpireType);
            sbRpt.AppendFormat(" and 归还时间 < '{0}'", dateExpire);
            sbRpt.Append(" group by 制表单位,借款类型");
            //he = sbRpt.ToString();
            try {
                cmd.Dispose();
                //此处如果不清空ds，则每次循环，都会往里面【追加】查询的数据
                //但是dt每次都读的第1条，所以追加数据无效
                ds.Clear();
                cmd.CommandText = sbRpt.ToString();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
                dt = ds.Tables[0];
                dr = dt.Rows[0];
                conn.Close();
                return Convert.ToDouble(dr["超期未还"].ToString());
                //取数
            }
            catch {
                conn.Close();
                return 0.00;
            }
        }

        /// <summary>
        /// 川渝区：按品类，取多个公司范围+借款类型的超期未还金额（蛋品平台的数据无效）
        /// </summary>
        /// <param name="dateExpire"></param>
        /// <param name="sbExpireVariety"></param>
        /// <param name="sbExpireCompany"></param>
        /// <returns></returns>
        public double GetExpireLimit(DateTime dateExpire, string sbExpireVariety, StringBuilder sbExpireCompany, string sbExpireType) {
            StringBuilder sbRpt = new StringBuilder("select 品类,借款类型,SUM(欠款余额) 超期未还 from View_ES_客户借贷款明细");
            sbRpt.AppendFormat(" where 制表单位 in ({0})", sbExpireCompany.ToString());
            sbRpt.AppendFormat(" and 品类 = '{0}'", sbExpireVariety);
            sbRpt.AppendFormat(" and 借款类型 = '{0}'", sbExpireType);
            sbRpt.AppendFormat(" and 归还时间 < '{0}'", dateExpire);
            sbRpt.Append(" group by 品类,借款类型");
            sbRpt.Append(" order by 品类,借款类型 desc");
            //he = sbRpt.ToString();
            try {
                cmd.Dispose();
                //此处如果不清空ds，则每次循环，都会往里面【追加】查询的数据
                //但是dt每次都读的第1条，所以追加数据无效
                ds.Clear();
                cmd.CommandText = sbRpt.ToString();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
                dt = ds.Tables[0];
                dr = dt.Rows[0];
                conn.Close();
                return Convert.ToDouble(dr["超期未还"].ToString());
                //取数
            }
            catch {
                conn.Close();
                return 0.00;
            }
        }

        public void Close()
        {
            cmd.Dispose();
            conn.Close();
        }

    }
}